﻿Imports DTO
Imports System.Data.OleDb
Public Class DocGiaDAO
    Public Function LayThongTinDocGiaBangMaDocGia(ByVal maDocGia As Integer) As DocGiaDTO
        DataProvider.OpenConnecttion()
        Dim docgiaDTO As New DocGiaDTO
        Dim sql As String = "select MaDocGia,TenDocGia,MaLoaiDocGia,NgaySinh,DiaChi,Email,NgayLapThe,MaNhanVien from DOCGIA where MaDocGia = " & maDocGia.ToString()
        Dim reader As OleDbDataReader
        reader = DataProvider.ExecSelect(sql)
        reader.Read()
        docgiaDTO.MaDocGia = reader.GetInt32(0)
        docgiaDTO.TenDocGia = reader.GetString(1)
        docgiaDTO.MaLoaiDocGia = reader.GetInt32(2)
        docgiaDTO.NgaySinh = reader.GetDateTime(3)
        docgiaDTO.DiaChi = reader.GetString(4)
        docgiaDTO.Email = reader.GetString(5)
        docgiaDTO.NgayLapThe = reader.GetDateTime(6)
        docgiaDTO.MaNhanVien = reader.GetInt32(7)
        DataProvider.CloseConnect()
        Return docgiaDTO
    End Function

    Public Function DocFileExcel(ByVal sql As String, ByVal path As String) As DataSet
        ExcelProvider.OpenConnection(path)
        Dim dt As New DataSet
        dt = ExcelProvider.ExecSelect(sql)
        ExcelProvider.CloseConnection()
        Return dt
    End Function

    Public Function LuuExcelvaoCSDL(ByVal dt As DataTable) As Integer
        Dim conn As OleDbConnection = DataProvider.OpenConnecttion()
        Dim adp As New OleDbDataAdapter("select * DOCGIA", conn)
        Dim build As New OleDbCommandBuilder(adp)
        Return adp.Update(dt)
    End Function

    Public Function LayDSDocGiaTable() As DataTable
        Dim sql As String = "select * from DOCGIA"
        Dim conn As OleDbConnection = DataProvider.OpenConnecttion()
        Dim adp As OleDbDataAdapter = New OleDbDataAdapter(sql, conn)
        Dim dt As New DataTable()
        adp.Fill(dt)
        DataProvider.CloseConnect()
        Return dt
    End Function

    Public Function BuildSql(ByVal docgia As DocGiaCRT, ByVal cn As OleDbConnection) As OleDbCommand

        Dim cmd As New OleDbCommand()
        Dim strDKMaDoGia = " 1=1 "
        Dim strDKTenDocGia = " 1=1 "
        Dim strDKNgaySinh = " 1=1 "
        Dim strDKNgayLapThe = " 1=1 "
        Dim strDKDiaChi = " 1=1 "
        Dim strDKEmail = " 1=1 "
        Dim strDKMaLoaiDocGia = " 1=1 "

        If (docgia.MaDocGia <> 0) Then
            strDKMaDoGia = " MaDocGia = ? "
            cmd.Parameters.Add("@MaDocGia", OleDbType.Integer)
            cmd.Parameters("@MaDocGia").Value = docgia.MaDocGia
        End If

        If (docgia.TenDocGia <> "") Then
            strDKTenDocGia = " TenDocGia like ?"
            cmd.Parameters.Add("@TenDocGia", OleDbType.WChar)
            cmd.Parameters("@TenDocGia").Value = "%" + docgia.TenDocGia + "%"
        End If

        If (docgia.ChkNgaySinh) Then
            strDKNgaySinh = " NgaySinh between ? and ? "
            cmd.Parameters.Add("@NgaySinhTu", OleDbType.Date)
            cmd.Parameters.Add("@NgaySinhDen", OleDbType.Date)
            cmd.Parameters("@NgaySinhTu").Value = docgia.NgaySinhTu
            cmd.Parameters("@NgaySinhDen").Value = docgia.NgaySinhDen
        End If

        If (docgia.ChkNgayLapThe) Then
            strDKNgayLapThe = " NgayLapThe between ? and ? "
            cmd.Parameters.Add("@NgayLapTheTu", OleDbType.Date)
            cmd.Parameters.Add("@NgayLapTheDen", OleDbType.Date)
            cmd.Parameters("@NgayLapTheTu").Value = docgia.NgayLapTheTu
            cmd.Parameters("@NgayLapTheDen").Value = docgia.NgayLapTheDen
        End If

        If (docgia.DiaChi <> "" And docgia.ChkDiaChi) Then
            strDKDiaChi = " DiaChi like ?"
            cmd.Parameters.Add("@DiaChi", OleDbType.WChar)
            cmd.Parameters("@DiaChi").Value = "%" + docgia.DiaChi + "%"
        End If

        If (docgia.Email <> "" And docgia.ChkEmail) Then
            strDKEmail = " Email like ?"
            cmd.Parameters.Add("@Email", OleDbType.WChar)
            cmd.Parameters("@Email").Value = "%" + docgia.Email + "%"
        End If

        If (docgia.ChkLoaiDocGia) Then
            strDKMaLoaiDocGia = " MaLoaiDocGia = ? "
            cmd.Parameters.Add("@MaLoaiDocGia", OleDbType.Integer)
            cmd.Parameters("@MaLoaiDocGia").Value = docgia.MaLoaiDocGia
        End If

        Dim strDKWhere As String = " Where "
        strDKWhere += strDKMaDoGia
        strDKWhere += " and " + strDKTenDocGia
        strDKWhere += " and " + strDKNgaySinh
        strDKWhere += " and " + strDKNgayLapThe
        strDKWhere += " and " + strDKDiaChi
        strDKWhere += " and " + strDKEmail
        strDKWhere += " and " + strDKMaLoaiDocGia
        Dim strSQL As String = "Select * From DOCGIA "
        strSQL += strDKWhere
        strSQL += " Order by MaDocGia "

        cmd.Connection = cn
        cmd.CommandText = strSQL
        Return cmd

    End Function

    Public Function TimKiem(ByVal hsCrt As DocGiaCRT) As DataTable
        Dim dt As New DataTable()
        Dim cn As OleDbConnection
        cn = DataProvider.OpenConnecttion()
        Dim cmd As OleDbCommand = BuildSql(hsCrt, cn)
        Dim da As New OleDbDataAdapter(cmd)
        da.Fill(dt)
        Return dt
    End Function

    Public Function LayDSDocGia() As List(Of DocGiaDTO)
        Dim sql As String = "Select MaDocGia,TenDocGia,NgaySinh,DiaChi,Email,NgayLapThe,MaLoaiDocGia,MaNhanVien from DOCGIA"
        DataProvider.OpenConnecttion()
        Dim reader As OleDbDataReader = DataProvider.ExecSelect(sql)
        Dim ds As New List(Of DocGiaDTO)()
        While reader.Read()
            Dim dg As New DocGiaDTO()
            dg.MaDocGia = reader.GetInt32(0)
            dg.TenDocGia = reader.GetString(1)
            dg.NgaySinh = reader.GetDateTime(2)
            dg.DiaChi = reader.GetString(3)
            dg.Email = reader.GetString(4)
            dg.NgayLapThe = reader.GetDateTime(5)
            dg.MaLoaiDocGia = reader.GetInt32(6)
            dg.MaNhanVien = reader.GetInt32(7)
            ds.Add(dg)
        End While
        Return ds
    End Function

    Public Function ThemDocGIa(ByVal sql As String) As Integer
        DataProvider.OpenConnecttion()
        Dim kq As Integer = DataProvider.ExecInsert(sql)
        DataProvider.CloseConnect()
        Return kq
    End Function

    Public Function CapNhatDocGia(ByVal sql As String) As Integer
        DataProvider.OpenConnecttion()
        Dim kq As Integer = DataProvider.ExecInsert(sql)
        DataProvider.CloseConnect()
        Return kq
    End Function

    Public Function LaySoTienNo(ByVal MaDG As Integer) As Integer
        DataProvider.OpenConnecttion()
        Dim sql As String = "select Top 1 TongNo from PHIEUTRASACH where MaDocGia = " + MaDG.ToString() + " Order by NgayTra desc"
        Dim reader As OleDbDataReader = DataProvider.ExecSelect(sql)
        Dim tienno As Integer
        While reader.Read()
            tienno = reader.GetInt32(0)
        End While
        DataProvider.CloseConnect()
        Return tienno
    End Function

    Public Function ThemPhieuThuTienPhat(ByVal sql As String) As Integer
        DataProvider.OpenConnecttion()
        Dim kq As Integer = DataProvider.ExecInsert(sql)
        DataProvider.CloseConnect()
        Return kq
    End Function
End Class
